package foo.dao;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import foo.base.BaseJdbcDAO;
import foo.workLlog.vo.WorkLogVvo;

@Repository
public class WorkLogDAO extends BaseJdbcDAO {
	public List<WorkLogVvo> findWorkLog(Date startDate, Date endDate,
			Collection<String> workers) {
		StringBuilder sql = new StringBuilder(" SELECT ");
		sql.append(" work_log.sn work_log_sn ,  work_log.work_date work_date,  work_log.worker worker, ");
		sql.append("  work_log.hr hr,  work_log.dscr work_log_dscr,  task.wbs_name AS wbs_name, ");
		sql.append(" task.proj_name AS proj_name,  task.proj_sn proj_sn,  task.wbs_content wbs_content, ");
		sql.append(" get_dscr( task.wbs_type_id ) wbs_type_dscr,  work_log.content work_log_content, work_log.wbs_sn wbs_sn ");

		sql.append(" FROM work_log LEFT JOIN  ");
		sql.append(" (SELECT wbs.SN wbs_sn , proj.sn proj_sn, wbs.name wbs_name , ");
		sql.append(" proj.NAME proj_name , wbs.type_id wbs_type_id,	wbs.content wbs_content ");
		sql.append(" FROM wbs,    proj ");
		sql.append(" WHERE wbs.proj_sn =proj.sn) task  ");

		sql.append(" ON task.wbs_sn=work_log.wbs_sn ");
		sql.append(" LEFT JOIN sys_user ON work_log.worker= sys_user.user_id ");
		sql.append(" WHERE work_log.WORK_DATE BETWEEN ? AND ? ");
		List<Object> params = new ArrayList<Object>();
		params.add(startDate);
		params.add(endDate);
		if (workers.size() != 0) {
			sql.append(" AND ");
			Iterator<String> it = workers.iterator();
			boolean hasNext;
			while (hasNext = it.hasNext()) {
				String worker = it.next();
				sql.append("( work_log.worker=?  ");
				if (hasNext) {
					sql.append(" OR ");
				}
				params.add(worker);
			}
		}
		String sqlStr = sql.toString();
		logger.debug("QUERY SQL:" + sqlStr);
		List<WorkLogVvo> query = jdbcTemplate.query(sqlStr,
				BeanPropertyRowMapper.newInstance(WorkLogVvo.class),
				params.toArray());
		return query;
	}

	public void insertWorLog(Date workDate, Integer wbsSn, String content,
			Integer hr, String worker) {
		Integer sn = getSn();
		StringBuilder sb = new StringBuilder();
		sb.append(" INSERT INTO work_log ");
		sb.append(" (sn, work_date, wbs_sn, content, hr, worker) ");
		sb.append(" VALUES (?, ?, ?, ?, ?, ?) ");
		String sql = sb.toString();
		logger.debug("執行SQL: " + sql);
		jdbcTemplate.update(sql, new Object[] { sn, workDate, wbsSn, content,
				hr, worker });
	}

	public void updateWorkLog(Integer workLogSn, Date workDate, Integer wbsSn,
			String workLogContent, String dscr, Integer hr) {
		StringBuilder sb = new StringBuilder(" UPDATE work_log SET ");
		ArrayList<Object> params = new ArrayList<Object>();
		if(workDate!=null){
		sb.append(" work_date=?,  ");
		params.add(workDate);
		}
		if(wbsSn!=null){
			sb.append("wbs_sn  =?, ");
			params.add(wbsSn);
		}
		if(workLogContent!=null){
			sb.append("content =?, ");
			params.add(workLogContent);
		}
		if(dscr!=null){
			sb.append(" dscr  =?, ");
			params.add(dscr);
		}
		if(hr!=null){
			sb.append(   " hr  =? , ");
			params.add(hr);
		}
		//移除最後一個逗號
		sb.deleteCharAt(sb.lastIndexOf(","));
		sb.append(" WHERE sn =? ");
		params.add(workLogSn);
		String sql = sb.toString();
		logger.debug("執行SQL:" + sql);
		jdbcTemplate.update(sql, params.toArray());
	}

	public List<Map<String, Object>> findPickWbsTaskNames(String searcWord) {
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT proj.NAME projName, wbs.NAME wbsName, wbs.CONTENT wbsContent ");
		sb.append(" FROM wbs wbs , proj proj ");
		sb.append(" WHERE proj.SN=wbs.PROJ_SN ");
		sb.append(" AND (proj.NAME LIKE ? OR wbs.name LIKE ? OR wbs.CONTENT LIKE ?) ");
		String sql = sb.toString();
		logger.debug("執行SQL:" + sql);
		List<Map<String, Object>> queryResult = jdbcTemplate.queryForList(sql,
				new Object[] { "%" + searcWord + "%", "%" + searcWord + "%",
						"%" + searcWord + "%" });
		return queryResult;

	}
}
